﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SmashedApples.Data.Commands;
using SmashedApples.Data.Contexts;
using System.Collections;
using SmashedApples.Utils;

namespace SmashedApples.Data
{
    public static class CommandBuilder
    {



        public static SelectContext buildSelectCommand(SelectContext context) 
        {
            context.sqlCommand = new SqlCommand();

            StringBuilder commandText = new StringBuilder("SELECT ");

            commandText.Append(context.command.columns);
            commandText.Append(" FROM ");
            commandText.Append(context.command.table);
            commandText.Append(" WHERE 1=1 ");

            if (context.command.where != null)
                commandText.Append(context.command.where + " ");


            if (context.command.parameters != null)
            {
                foreach (DictionaryEntry i in context.command.parameters)
                {
                    String name = (i.Key as String).ToUpper();
                    if ( context.command.likeColumns.Contains(name)  )
                    {
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = "@" + i.Key;
                        param.Value = DataUtils.CreateLikeValue(i.Value.ToString());
                        context.sqlCommand.Parameters.Add(param);

                        commandText.Append("AND UPPER(" + name + ") like @" + name + " ");
                    }
                    else if ( context.command.inColumns.Contains(name))
                    {
                        commandText.Append("AND " + name + " in (");
                        List<String> incols = SmashedUtils.StringToList(i.Value.ToString());
                        for (int count = 0; count < incols.Count; count++)
                        {
                            if (count == 0)
                            {
                                commandText.Append("'" + incols[count] + "'");
                            }
                            else
                            {
                                commandText.Append(", '" + incols[count] + "'");
                            }
                        }
                        commandText.Append(") ");
                    }
                    else
                    {
                        // Determine if parameter is a bind variable by checking where clause, if set
                        SqlParameter param = new SqlParameter();
                        String BindVar = "@" + name;
                        if (context.command.where == null)
                        {
                            commandText.Append("AND " + name + " = @" + name + " ");
                        }
                        else if (!context.command.where.Contains(BindVar))
                        {
                            commandText.Append("AND " + name + " = @" + name + " ");
                        }

                        param.ParameterName = "@" + name;
                        param.Value = i.Value;
                        context.sqlCommand.Parameters.Add(param);
                    }
                }
            }

            if ( !String.IsNullOrEmpty(context.command.orderBy) )
            {
                commandText.Append(" ORDER BY ");
                commandText.Append(context.command.orderBy);
            }


            if (context.debug)
                context.response.info.queries.Add(commandText.ToString());

            context.sqlCommand.CommandText = commandText.ToString();


   
            return context;   
        }

        public static DeleteContext buildDeleteCommand(DeleteContext context)
        {
            context.sqlCommand = new SqlCommand();
            StringBuilder commandText = new StringBuilder();

            if ( context.command.pks != null)
            {
                commandText = commandText.Append("DELETE " + context.command.table.ToUpper() + " WHERE ");
                commandText.Append(" 1=1 ");
                commandText.Append("AND " + context.command.pk.ToUpper() + " in (");

				for (int count = 0; count < context.command.pks.Count; count++)
				{	
					if (count == 0)
					{
                        commandText.Append("'" + context.command.pks[count] + "'");
					}
					else
					{
                        commandText.Append(", '" + context.command.pks[count] + "'");
					}
				}
                commandText.Append(") ");

                if (context.debug)
                    context.response.info.queries.Add(commandText.ToString());

                context.sqlCommand.CommandText = commandText.ToString();
            }
            return context;
        }


        public static SaveContext buildInsertCommand(SaveContext context)
        {
          
            context.sqlCommand = new SqlCommand();

            String columnName = null;
            StringBuilder commandText = new StringBuilder("SET NOCOUNT ON; ");
            StringBuilder valuesClause = new StringBuilder(") VALUES (");

            if (context.command.parameters != null)
            {
                    commandText.Append("INSERT INTO " + context.command.table.ToUpper() + " (");

                    foreach (DictionaryEntry i in context.command.parameters)
                    {
                        columnName = (i.Key as String).ToUpper();
                        commandText.Append(columnName + ",");	// build list of columns being inserted
                        valuesClause = valuesClause.Append("@" + columnName + ",");
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = "@" + columnName;
                        param.Value = i.Value;
                        context.sqlCommand.Parameters.Add(param);
                    }
                    commandText.Remove((commandText.Length - 1), 1);  // remove trailing comma from Column list
                    valuesClause.Remove((valuesClause.Length - 1), 1);	// remove trailing comma from Values list
                    commandText.Append(valuesClause + ") ");
                    commandText.Append("SELECT @@IDENTITY AS 'PK' ");
                    commandText.Append("SET NOCOUNT OFF"); 

                    if (context.debug)
                        context.response.info.queries.Add(commandText.ToString());

                    context.sqlCommand.CommandText = commandText.ToString();              
            }
            return context;
        }

       



        public static SaveContext buildUpdateCommand(SaveContext context)
		{

            context.sqlCommand = new SqlCommand();

            if (context.command.parameters != null)
			{
			    String columnName = null;
                StringBuilder commandText = new StringBuilder();

                commandText = commandText.Append("UPDATE " + context.command.table.ToUpper() + " SET ");
				StringBuilder whereClause = new StringBuilder(" WHERE ");

                foreach (DictionaryEntry i in context.command.parameters)
                {
                    columnName = (i.Key as String).ToUpper();

                    SqlParameter param = new SqlParameter();
                    param.ParameterName = "@" + columnName;
                    param.Value = i.Value;
                    context.sqlCommand.Parameters.Add(param);

                    if (columnName == context.command.pk.ToUpper())
                    {

                        whereClause.Append(columnName + " = @" + columnName); // build pk portion
                    }
                    else
                    {
                        commandText.Append(columnName + " = @" + columnName + ",");	// build "SET" portion
                    }
                }

                commandText.Remove((commandText.Length - 1), 1);  // remove trailing comma from "SET" portion
                commandText.Append(whereClause); // append where clause
                context.sqlCommand.CommandText = commandText.ToString();

                if (context.debug)
                    context.response.info.queries.Add(commandText.ToString());


			}
			return context;
		}


        public static SaveContext buildSelectByPKCommand(SaveContext context)
        {
            context.sqlCommand = new SqlCommand();


            return context;
        }




    }
}
